package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DBEmployee {

    private Connection con;

    // Constructor
    public DBEmployee(){
        con = DbConnection1.getInstance().getDBcon();
    }
    
    public ArrayList<Employee> getAllEmployees()
    {
        return miscWhere("");
    }
    
    
    public int insertEmployee(Employee emp) throws Exception
    {  
        int nextIdEmployee = GetMax.getMaxId("Select max(idEmployee) from employee");
        nextIdEmployee = nextIdEmployee + 1;
        System.out.println("next idEmployee = " +  nextIdEmployee);
  
       int rc = -1;
	   String query="INSERT INTO employee(idEmployee, cpr, hourlyWage, bankAccountNo,name,address, zipCode, city, phoneNo, email)  VALUES('"+
			nextIdEmployee  + "','"  +
			emp.getCpr()   + "','"  +
		    emp.getHourlyWage()  + "','"  +
		    emp.getBankAccountNo()  + "','"  +
		    emp.getName()  + "','"  +
		    emp.getAddress()  + "','"  +
		    emp.getZipCode() + "','" +
		    emp.getCity()  + "','"  +
		    emp.getPhoneNo()  + "','"  +
		    emp.getEmail()   + "')";
            
                    

       System.out.println("insert : " + query);
       try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
       }//end try
       catch(SQLException ex){
          System.out.println("Employee not created");
          throw new Exception ("Employee is not inserted correct");
       }
       return(rc);
    }
    
    public Employee findEmployeeByName(String name)
    {   String wClause = "  name = '" + name + "'";
        return singleWhere(wClause);
    }
    
    public Employee findEmployeeById(String idEmployee)
    {   String wClause = "  idEmployee = '" + idEmployee + "'";
        return singleWhere(wClause);
    }
    
    public int updateEmployee(Employee emp)
	{
		Employee empObj  = emp;
		int rc=-1;

		String query="UPDATE employee SET "+
		 	"cpr ='"+ empObj.getCpr()+"', "+
		 	"hourlyWage ='"+ empObj.getHourlyWage() + "', " +
            "bankAccountNo ='"+ empObj.getBankAccountNo() + "', " +
            "name ='"+ empObj.getName() + "', " +
            "address ='"+ empObj.getAddress() + "', " +
            "city ='"+ empObj.getCity() + "', " +
            "phoneNo ='"+ empObj.getPhoneNo() + "', " +
            "email ='"+ empObj.getEmail() + "' " +
            " WHERE idEmployee = '"+ empObj.getIdEmployee() + "'";
			System.out.println("Update query:" + query);
			
  		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in employee db: "+ex);
	  	}
		return(rc);
	}
    
    public int deleteEmployee(String idEmployee)
	{
        int rc=-1;
	  
	  	String query="DELETE FROM employee WHERE idEmployee = '" + idEmployee + "'";
        System.out.println(query);
	  	
        try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
        catch(Exception ex)
        {
	 	  	System.out.println("Delete exception in employee db: "+ex);
        }
		return(rc);
	}
    
    
    
    
    
    
    /* singleWhere, miscWhere, queries*/
    private ArrayList<Employee> miscWhere(String wClause)
	{
        ResultSet results;
	    ArrayList<Employee> list = new ArrayList<Employee>();	
		
	    String query =  buildQuery(wClause);
  
        try{ // read the employee from the database
			Statement stmt = con.createStatement();
		 	stmt.setQueryTimeout(5);
		 	results = stmt.executeQuery(query);
		 	
		
			while( results.next() )
			{
		     	 Employee empObj = new Employee();
		     	 empObj = buildEmployee(results);	
                 list.add(empObj);	
			}
	        stmt.close();   
	        //                 if(retrieveAssociation)
	//                 {   //The supervisor and department is to be build as well
	//                     for(Employee empObj : list){
	//                        String superssn = empObj.getSupervisor().getSsn();
	//                        Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
	//                        empObj.setSupervisor(superEmp);
	//                        System.out.println("Supervisor is seleceted");
	//                       // here the department has to be selected as well
	//                     }
	//                 }//end if   
				
		}
        catch(Exception e)
    	{
    		System.out.println("Query exception - select: "+e);
    		e.printStackTrace();
    	}
        return list;
	 	
	}
    
    private Employee singleWhere(String wClause)
	{
		ResultSet results;
		Employee empObj = new Employee();
                
	    String query =  buildQuery(wClause);
        System.out.println(query);
		try{ // read the employee from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
                            empObj = buildEmployee(results);
                            //assocaition is to be build
                            stmt.close();
//                            if(retrieveAssociation)
//                            {   //The supervisor and department is to be build as well
//                                String superssn = empObj.getSupervisor().getSsn();
//                                Employee superEmp = singleWhere(" ssn = '" + superssn + "'",false);
//                                empObj.setSupervisor(superEmp);
//                                System.out.println("Supervisor is seleceted");
//                                System.out.println("Department is selected");
//                                DBDepartment dbObj = new DBDepartment();
//                                empObj.setDepartment(dbObj.findDepartment(empObj.getDept().getDnumber(), false));
//                                System.out.println("Department is selected");
//                           
//                           
//                            }
	 		}
            else
            	empObj = null;
            
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return empObj;
	}
    
    private String buildQuery(String wClause)
	{
	    String query="SELECT idEmployee, cpr, hourlyWage, bankAccountNo, name, address, city, phoneNo, email FROM employee";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
    
    private Employee buildEmployee(ResultSet results)
    {   
    	Employee empObj = new Employee();
        try
        { // the columns from the table employee  are used
    		empObj.setIdEmployee(results.getString("idEmployee"));
    		empObj.setCpr(results.getString("cpr"));
    		empObj.setHourlyWage(results.getDouble("hourlyWage"));
    		empObj.setBankAccountNo(results.getString("bankAccountNo"));
			empObj.setName(results.getString("name"));
			empObj.setAddress(results.getString("address"));
			empObj.setCity(results.getString("city"));
			empObj.setPhoneNo(results.getString("phoneNo"));
			empObj.setEmail(results.getString("email"));
        }
       catch(Exception e)
       {
           System.out.println("error in building the employee object");
       }
       return empObj;
    }

}
